package com.apes.scm.stock.safetyStock.repository;


import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.stock.safetyStock.model.SafetyStockGap;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 商品库存帐
 * Created by HuJianlin on 2018-01-27.
 */
public interface SafetyStockGapRepository extends CustomRepository<SafetyStockGap, Long> {
    List<SafetyStockGap> findAllByStatisticsDate(Date date);

    @Query(value = "" +
            "SELECT * FROM (" +
            "SELECT A.*, DENSE_RANK() OVER(ORDER BY A.LOCATION_ID ASC) ORDERBY " +
            "  FROM (SELECT DISTINCT B.F_LOCATION_ID, A.LOCATION_ID, B.SAFETY_STOCK_MARK " +
            "           FROM STOCK_SAFETY_GAP A, MAS_DC_DISTRIBUTION_RANGE B " +
            "          WHERE A.LOCATION_ID = B.C_LOCATION_ID " +
            "            AND SAFETY_STOCK_MARK > 0 " +
            "            AND STATISTICS_DATE = GET_DATE() AND DONE = 0 " +
            "            AND EXISTS (SELECT 1 " +
            "                   FROM V_GET_DEPOT_LEVEL " +
            "                  WHERE ID = B.C_LOCATION_ID " +
            "                    AND DEPOT_LEVEL = :depotLevel)) A " +
            " ORDER BY LOCATION_ID, SAFETY_STOCK_MARK DESC )" +
            " WHERE ORDERBY = 1 ", nativeQuery = true)
    List<Map> getLocation(@Param("depotLevel") String depotLevel);

    @Query(value = "" +
            "SELECT * " +
            "  FROM (SELECT A.*, DENSE_RANK() OVER(ORDER BY A.LOCATION_ID ASC) ORDERBY " +
            "           FROM (SELECT DISTINCT B.F_LOCATION_ID, A.LOCATION_ID, " +
            "                                  B.SAFETY_STOCK_MARK " +
            "                    FROM STOCK_SAFETY_GAP A, MAS_DC_DISTRIBUTION_RANGE B " +
            "                   WHERE A.LOCATION_ID = B.C_LOCATION_ID " +
            "                     AND SAFETY_STOCK_MARK > 0 " +
            "                     AND STATISTICS_DATE = GET_DATE() " +
//            "                     AND EXISTS (SELECT 1 " +
//            "                            FROM V_GET_DEPOT_LEVEL " +
//            "                           WHERE ID = B.C_LOCATION_ID " +
//            "                             AND DEPOT_LEVEL = :depotLevel)" +
            " ) A " +
            "          WHERE NOT EXISTS (SELECT 1 " +
            "                   FROM STOCK_GAP_DISTRIBUTION_INFO J " +
            "                  WHERE J.F_LOCATION_ID = A.F_LOCATION_ID " +
            "                    AND J.LOCATION_ID = A.LOCATION_ID " +
            "                    AND EXECUTE_DATA = GET_DATE()) " +
            "          ORDER BY LOCATION_ID, SAFETY_STOCK_MARK DESC) " +
            " WHERE ORDERBY = 1 AND ROWNUM = 1 ", nativeQuery = true)
    Map findCanExecuteLocation(/*@Param("depotLevel") String depotLevel*/);

    @Query(value = "" +
            "SELECT * FROM (" +
            "SELECT A.*, DENSE_RANK() OVER(ORDER BY A.LOCATION_ID ASC) ORDERBY " +
            "  FROM (SELECT DISTINCT B.F_LOCATION_ID, A.LOCATION_ID, B.SAFETY_STOCK_MARK " +
            "           FROM STOCK_SAFETY_GAP A, MAS_DC_DISTRIBUTION_RANGE B " +
            "          WHERE A.LOCATION_ID = B.C_LOCATION_ID " +
            "            AND SAFETY_STOCK_MARK > 0 " +
            "            AND STATISTICS_DATE = GET_DATE() AND A.LOCATION_ID = '6128002011' " +
            "            AND EXISTS (SELECT 1 " +
            "                   FROM V_GET_DEPOT_LEVEL " +
            "                  WHERE ID = B.C_LOCATION_ID )) A " +
            " ORDER BY LOCATION_ID, SAFETY_STOCK_MARK DESC )" +
            " WHERE ORDERBY = 1 ", nativeQuery = true)
    List<Map> testgetLocation();

    @Query(value = "SELECT * FROM STOCK_SAFETY_GAP WHERE TOTAL_QTY - ASSIGNED_QTY > 0 AND LOCATION_ID = :cLocationId AND STATISTICS_DATE = GET_DATE() ", nativeQuery = true)
    List<Map> qrySafetyStockGapItem(@Param("cLocationId") String cLocationId);

    @Query(value = "" +
            "SELECT * " +
            "  FROM (SELECT A.*, ROWNUM XH, TO_CHAR(STATISTICS_DATE, 'YYYY-MM-DD') STATISTICS_DATE_CHAR " +
            "           FROM STOCK_SAFETY_GAP A " +
            "          WHERE LOCATION_ID = ?1 " + // TOTAL_QTY - ASSIGNED_QTY > 0
            "            AND STATISTICS_DATE = GET_DATE()) " +
            " WHERE XH > ?2 " +
            "   AND XH <= ?3 ", nativeQuery = true)
    List<Map> qrySafetyStockGapItemPage(String cLocationId, int prePage, int nextPage);

    @Query(value = "SELECT * FROM STOCK_SAFETY_GAP WHERE TOTAL_QTY - ASSIGNED_QTY > 0 AND LOCATION_ID = :cLocationId AND STATISTICS_DATE = GET_DATE() ", nativeQuery = true)
    List<Map> testqrySafetyStockGapItem(@Param("cLocationId") String cLocationId);

    @Query(value = "" +
            "SELECT STATISTICS_DATE ID, ID ITEM_ID, A.INNER_BUSINESS_TYPE, " +
            "       (SELECT PRODUCT_UOM_ID FROM MAS_PRODUCT WHERE ID = A.PRODUCT_ID) PRODUCT_UOM_ID, " +
            "       TOTAL_QTY QTY_REQUIRE, (TOTAL_QTY - ASSIGNED_QTY) RABBET " +
            "  FROM STOCK_SAFETY_GAP A " +
            " WHERE TOTAL_QTY - ASSIGNED_QTY > 0 AND STATISTICS_DATE = GET_DATE() " +
            "   AND LOCATION_ID = :cLocationId AND PRODUCT_ID = :productId", nativeQuery = true)
    List<Map> qrySafetyStockGaps(@Param("cLocationId") String cLocationId, @Param("productId") String productId);

    @Query(value = "SELECT * FROM STOCK_SAFETY_GAP WHERE ID = :id AND STATISTICS_DATE = GET_DATE() ", nativeQuery = true)
    Map accordingIdQuery(@Param("id") Long id);

    @Query(value = "" +
            "SELECT (SELECT DEPOT_LEVEL FROM V_GET_DEPOT_LEVEL WHERE ID = T.LOCATION_ID) DEPOT_LEVEL " +
            "  FROM STOCK_SAFETY_GAP T, MAS_DC_DISTRIBUTION_RANGE B " +
            " WHERE T.LOCATION_ID = B.C_LOCATION_ID " +
            "   AND STATISTICS_DATE = GET_DATE() " +
            "   AND SAFETY_STOCK_MARK > 0 " +
            "   AND NOT EXISTS (SELECT LOCATION_ID " +
            "          FROM STOCK_GAP_DISTRIBUTION_INFO J " +
            "         WHERE J.F_LOCATION_ID = B.F_LOCATION_ID " +
            "           AND J.LOCATION_ID = T.LOCATION_ID " +
            "           AND EXECUTE_DATA = GET_DATE()) " +
            "   AND ROWNUM = 1 ", nativeQuery = true)
    List<Map> existsExecuData(/*String depotLevel*/);

    @Query(value = "" +
            " SELECT B.DEPOT_LEVEL " +
            "  FROM MAS_LOCATION A, MAS_DEPOT B " +
            " WHERE A.DEPOT_ID = B.ID " +
            "   AND A.ID IN (SELECT LOCATION_ID " +
            "                  FROM STOCK_GAP_DISTRIBUTION_INFO J " +
            "                 WHERE EXECUTE_DATA = GET_DATE()) ", nativeQuery = true)
    List<Map> findStoreLevels();

}





























